﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using nvn.Core.MVP;
using Hutech.CRM.Library.Models.Entities;

namespace Hutech.CRM.Library.Models.DataAccessLayer
{
    public class AllCategoriesDAL
    {
        
        #region Template
        /// <summary>
        /// Inserts a row in the other_AllCategories table. Returns rows affected count.
        /// </summary>
        /// <param name="itemGuid"> itemGuid </param>
        /// <param name="itemName"> itemName </param>
        /// <param name="kindGuid"> kindGuid </param>
        /// <param name="kindName"> kindName </param>
        /// <param name="updatedUser"> updatedUser </param>
        /// <param name="updatedDate"> updatedDate </param>
        /// <param name="isActive"> isActive </param>
        /// <returns>int</returns>
        public static int Create(AllCategories item)
        {
            SqlParameterHelper sph = new SqlParameterHelper(ConnectionStringStatic.GetWriteConnectionString(), "other_AllCategories_Insert", 7);
            sph.DefineSqlParameter("@ItemGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, item.ItemGuid);
            sph.DefineSqlParameter("@ItemName", SqlDbType.NVarChar, 256, ParameterDirection.Input, item.ItemName);
            sph.DefineSqlParameter("@KindGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, item.KindGuid);
            sph.DefineSqlParameter("@KindName", SqlDbType.NVarChar, 256, ParameterDirection.Input, item.KindName);
            sph.DefineSqlParameter("@UserUpdated", SqlDbType.UniqueIdentifier, ParameterDirection.Input, item.UserUpdated);
            sph.DefineSqlParameter("@LastUpdatedDate", SqlDbType.DateTime, ParameterDirection.Input, item.LastUpdatedDate);
            sph.DefineSqlParameter("@IsActive", SqlDbType.Bit, ParameterDirection.Input, item.IsActive);
            int rowsAffected = sph.ExecuteNonQuery();
            return rowsAffected;

        }


        /// <summary>
        /// Updates a row in the other_AllCategories table. Returns true if row updated.
        /// </summary>
        /// <param name="itemGuid"> itemGuid </param>
        /// <param name="itemName"> itemName </param>
        /// <param name="kindGuid"> kindGuid </param>
        /// <param name="kindName"> kindName </param>
        /// <param name="updatedUser"> updatedUser </param>
        /// <param name="updatedDate"> updatedDate </param>
        /// <param name="isActive"> isActive </param>
        /// <returns>bool</returns>
        public static bool Update(AllCategories item)
        {
            SqlParameterHelper sph = new SqlParameterHelper(ConnectionStringStatic.GetWriteConnectionString(), "other_AllCategories_Update", 7);
            sph.DefineSqlParameter("@ItemGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, item.ItemGuid);
            sph.DefineSqlParameter("@ItemName", SqlDbType.NVarChar, 256, ParameterDirection.Input, item.ItemName);
            sph.DefineSqlParameter("@KindGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, item.KindGuid);
            sph.DefineSqlParameter("@KindName", SqlDbType.NVarChar, 256, ParameterDirection.Input, item.KindName);
            sph.DefineSqlParameter("@UserUpdated", SqlDbType.UniqueIdentifier, ParameterDirection.Input, item.UserUpdated);
            sph.DefineSqlParameter("@LastUpdatedDate", SqlDbType.DateTime, ParameterDirection.Input, item.LastUpdatedDate);
            sph.DefineSqlParameter("@IsActive", SqlDbType.Bit, ParameterDirection.Input, item.IsActive);
            int rowsAffected = sph.ExecuteNonQuery();
            return (rowsAffected > 0);

        }

        /// <summary>
        /// Deletes a row from the other_AllCategories table. Returns true if row deleted.
        /// </summary>
        /// <param name="itemGuid"> itemGuid </param>
        /// <returns>bool</returns>
        public static bool Delete(
            Guid itemGuid)
        {
            SqlParameterHelper sph = new SqlParameterHelper(ConnectionStringStatic.GetWriteConnectionString(), "other_AllCategories_Delete", 1);
            sph.DefineSqlParameter("@ItemGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, itemGuid);
            int rowsAffected = sph.ExecuteNonQuery();
            return (rowsAffected > 0);

        }

        /// <summary>
        /// Gets an IDataReader with one row from the other_AllCategories table.
        /// </summary>
        /// <param name="itemGuid"> itemGuid </param>
        public static IDataReader GetOne(
            Guid itemGuid)
        {
            SqlParameterHelper sph = new SqlParameterHelper(ConnectionStringStatic.GetReadConnectionString(), "other_AllCategories_SelectOne", 1);
            sph.DefineSqlParameter("@ItemGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, itemGuid);
            return sph.ExecuteReader();

        }

        /// <summary>
        /// Gets a count of rows in the other_AllCategories table.
        /// </summary>
        public static int GetCount()
        {

            return Convert.ToInt32(SqlHelper.ExecuteScalar(
                ConnectionStringStatic.GetReadConnectionString(),
                CommandType.StoredProcedure,
                "other_AllCategories_GetCount",
                null));

        }

        /// <summary>
        /// Gets an IDataReader with all rows in the other_AllCategories table.
        /// </summary>
        public static IDataReader GetAll()
        {

            return SqlHelper.ExecuteReader(
                ConnectionStringStatic.GetReadConnectionString(),
                CommandType.StoredProcedure,
                "other_AllCategories_SelectAll",
                null);

        }

        /// <summary>
        /// Gets a page of data from the other_AllCategories table.
        /// </summary>
        /// <param name="pageNumber">The page number.</param>
        /// <param name="pageSize">Size of the page.</param>
        /// <param name="totalPages">total pages</param>
        public static IDataReader GetPage(
            int pageNumber,
            int pageSize,
            out int totalPages)
        {
            totalPages = 1;
            int totalRows
                = GetCount();

            if (pageSize > 0) totalPages = totalRows / pageSize;

            if (totalRows <= pageSize)
            {
                totalPages = 1;
            }
            else
            {
                int remainder;
                Math.DivRem(totalRows, pageSize, out remainder);
                if (remainder > 0)
                {
                    totalPages += 1;
                }
            }

            SqlParameterHelper sph = new SqlParameterHelper(ConnectionStringStatic.GetReadConnectionString(), "other_AllCategories_SelectPage", 2);
            sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
            sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
            return sph.ExecuteReader();

        }

        #endregion

        #region Add by tsandtm
        internal static IDataReader GetAllGroup()
        {
            return SqlHelper.ExecuteReader(
                ConnectionStringStatic.GetReadConnectionString(),
                CommandType.StoredProcedure,
                "other_AllCategories_GetAllGroup",
                null);
        }
        internal static IDataReader GetAllByKindGuid(Guid kindGuid)
        {
            SqlParameterHelper sph = new SqlParameterHelper(ConnectionStringStatic.GetReadConnectionString(), "other_AllCategories_SelectAllByKindGuid", 1);
            sph.DefineSqlParameter("@KindGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, kindGuid);
            return sph.ExecuteReader();
        }

        #endregion






    }

}



